<?php

  function get_nhan_vien_tsx_autocomplete($id_tsx, $string) {
    $query = db_select('dt_nhan_vien', 'nv')->extend('PagerDefault');
    $query->fields('nv', array('ma_nhan_vien', 'ten_nhan_vien'));
    $query->condition(
      db_or()->
        condition('nv.ten_nhan_vien', '%' . db_like($string) . '%', 'LIKE')->
        condition('nv.ma_nhan_vien', $string, '=')->
        condition('nv.ma_nhan_vien', '%' . db_like($string) . '%', 'LIKE')
    );
    $query->condition('nv.trang_thai', 1);
    $query->condition('nv.id_tosanxuat', $id_tsx);
    $result = $query->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[$rs->ten_nhan_vien . ' [' . $rs->ma_nhan_vien . ']'] = $rs->ten_nhan_vien . ' [' . $rs->ma_nhan_vien . ']';
    }
    return $data;
  }
  function get_nhan_vien_autocomplete($loai, $string) {
    $query = db_select('dt_nhan_vien', 'nv')->extend('PagerDefault');
    $query->fields('nv', array('ma_nhan_vien', 'ten_nhan_vien'));
    $query->condition(
      db_or()->
        condition('nv.ten_nhan_vien', '%' . db_like($string) . '%', 'LIKE')->
        condition('nv.ma_nhan_vien', $string, '=')->
        condition('nv.ma_nhan_vien', '%' . db_like($string) . '%', 'LIKE')
    );
    $query->condition('nv.trang_thai', $loai);
    $result = $query->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[$rs->ten_nhan_vien . ' [' . $rs->ma_nhan_vien . ']'] = $rs->ten_nhan_vien . ' [' . $rs->ma_nhan_vien . ']';
    }
    return $data;
  }

  function load_select() {
    $list_loai = array();
    $query = db_select('dt_to_san_xuat', 'tsx');
    $query->fields('tsx', array('id', 'ten_to_san_xuat'));
    $result = $query->execute();
    while ($row = $result->fetchObject())
    {
      $list_loai[$row->id] = $row->ten_to_san_xuat;
    }
    return $list_loai;
  }

  function insert_nhan_vien($uid, $ten_nhan_vien, $chuc_vu, $loai, $so_dien_thoai, $dia_chi, $ghi_chu, $tien_luu_dau) {
    $thoi_gian = time();
    $trang_thai = 1;
    if (!$tien_luu_dau) {
      $tien_luu_dau = 0;
    }
    $query = db_insert('dt_nhan_vien');
    $arr = array(
      'uid' => $uid,
      'ma_nhan_vien' => '',
      'ten_nhan_vien' => $ten_nhan_vien,
      'chuc_vu' => $chuc_vu,
      'dia_chi' => $dia_chi,
      'so_dien_thoai' => $so_dien_thoai,
      'tien_luu' => $tien_luu_dau,
      'tien_luu_dau' => $tien_luu_dau,
      'id_tosanxuat' => $loai,
      'ghi_chu' => $ghi_chu,
      'trang_thai' => $trang_thai,
      'thoi_gian' => date('m/Y', $thoi_gian),
      'thoi_gian_ngung' => date('m/Y', $thoi_gian)
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_nhan_vien = $id;
    while (strlen($ma_nhan_vien) < 3) {
      $ma_nhan_vien = '0' . $ma_nhan_vien;
    }
    if ($loai != 0) {
      $to_san_xuat = get_to_san_xuat_theo_id($loai);
      $ma_nhan_vien = $to_san_xuat->ma_to_san_xuat . '_' . $ma_nhan_vien;
    } else
    {
      $ma_nhan_vien = 'VP_' . $ma_nhan_vien;
    }
    db_update('dt_nhan_vien')
      ->fields(array(
                    'ma_nhan_vien' => $ma_nhan_vien,
               ))
      ->condition('id', $id)
      ->execute();
  }

  function get_nhan_vien($trang_thai,$filter, $header = array()) {
    $query = db_select('dt_nhan_vien', 'nv')->extend('PagerDefault')->extend('TableSort');
    $query->leftJoin('dt_to_san_xuat', 'tsx', 'nv.id_tosanxuat=tsx.id');
    $query->fields('nv', array('id', 'ma_nhan_vien', 'ten_nhan_vien', 'chuc_vu', 'so_dien_thoai', 'dia_chi', 'ghi_chu', 'thoi_gian', 'id_tosanxuat', 'thoi_gian_ngung'));
    $query->fields('tsx', array('ten_to_san_xuat'));
    $query->condition('nv.trang_thai', $trang_thai);
    if ($filter['ma_ref']) {
        $query->condition('nv.ma_nhan_vien', $filter['ma_ref']);
      }
    $query->orderByHeader($header);
   // $query->orderBy('thoi_gian', 'DESC');
    $result = $query->limit(25)->execute();
    $data = array();
    $params = $_GET;
    unset($params['q']);
    $str_param = '';
    if (!empty($params)) {
      foreach ($params as $key => $val) {
        $str_param .= '&' . $key . '=' . $val;
      }
    }
    $chuc_vu = get_option_chuc_vu();
    $edit_perm = user_access('sua nhan vien');
    if ($trang_thai == 0) {
      while ($rs = $result->fetchObject()) {
        if ($trang_thai) {
          $link = '<a href="' . base_path() . 'nhan_vien/trang_thai/ngung/' . $rs->ma_nhan_vien . '?destination=nhan_vien' . $str_param . '">Ngưng công tác</a>';
        }
        else {
          $link = '<a href="' . base_path() . 'nhan_vien/trang_thai/tiep_tuc/' . $rs->ma_nhan_vien . '?destination=nhan_vien/xem_danh_sach/ngung_lam_viec' . $str_param . '">Tiếp tục công tác</a>';
        }
        $data[] = array(
          'ma_nhan_vien' => $rs->ma_nhan_vien,
          'ten_nhan_vien' => $rs->ten_nhan_vien,
          'so_dien_thoai' => $rs->so_dien_thoai,
          'chuc_vu' => $chuc_vu[$rs->chuc_vu],
          'id_tosanxuat' => $rs->id_tosanxuat != 0 ? $rs->ten_to_san_xuat : 'Nhân viên văn phòng',
          'thoi_gian' => $rs->thoi_gian,
          'thoi_gian_ngung' => $rs->thoi_gian_ngung,
          'thao_tac' => ($edit_perm ? l('Sửa', 'nhan_vien/sua_nhan_vien/' . $rs->ma_nhan_vien) : '') . ' | ' . $link
                        . dt_view_ghi_chu('NV_' . $rs->ma_nhan_vien, 'Xem thêm nhân viên ' . $rs->ten_nhan_vien, 'Địa chỉ: ' . nl2br($rs->dia_chi) . '<br />' . 'Ghi chú: ' . ($rs->ghi_chu
              ? nl2br($rs->ghi_chu) : 'Không có ghi chú'), true, 'Xem thêm')
        );
      }
    }
    else {
      while ($rs = $result->fetchObject()) {
        if ($trang_thai) {
          $link = '<a href="' . base_path() . 'nhan_vien/trang_thai/ngung/' . $rs->ma_nhan_vien . '?destination=nhan_vien' . $str_param . '">Ngưng công tác</a>';
        }
        else {
          $link = '<a href="' . base_path() . 'nhan_vien/trang_thai/tiep_tuc/' . $rs->ma_nhan_vien . '?destination=nhan_vien/xem_danh_sach/ngung_lam_viec' . $str_param . '">Tiếp tục công tác</a>';
        }
        $data[] = array(
          'ma_nhan_vien' => $rs->ma_nhan_vien,
          'ten_nhan_vien' => $rs->ten_nhan_vien,
          'so_dien_thoai' => $rs->so_dien_thoai,
          'chuc_vu' => $chuc_vu[$rs->chuc_vu],
          'id_tosanxuat' => $rs->id_tosanxuat != 0 ? $rs->ten_to_san_xuat : 'Nhân viên văn phòng',
          'thoi_gian' => $rs->thoi_gian,
          'thao_tac' => ($edit_perm ? l('Sửa', 'nhan_vien/sua_nhan_vien/' . $rs->ma_nhan_vien) : '') . ' | ' . $link
                        . dt_view_ghi_chu('NV_' . $rs->ma_nhan_vien, 'Xem thêm nhân viên ' . $rs->ten_nhan_vien, 'Địa chỉ: ' . nl2br($rs->dia_chi) . '<br />' . 'Ghi chú: ' . ($rs->ghi_chu
              ? nl2br($rs->ghi_chu) : 'Không có ghi chú'), true, 'Xem thêm')
        );
      }
    }
    return $data;
  }

  function get_nhan_vien_theo_ma($ma_nhan_vien) {
    $query = db_select('dt_nhan_vien', 'nv');
    $query->fields('nv', array('id', 'ma_nhan_vien', 'ten_nhan_vien', 'chuc_vu', 'id_tosanxuat', 'dia_chi', 'so_dien_thoai', 'ghi_chu','tien_luu'));
    $query->condition('nv.ma_nhan_vien', $ma_nhan_vien, '=');
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function update_nhan_vien($uid, $ma_nhan_vien, $ten_nhan_vien, $chuc_vu, $loai, $so_dien_thoai, $dia_chi, $ghi_chu) {
  //  $thoi_gian = time();
    db_update('dt_nhan_vien')
      ->fields(array(
                    'uid' => $uid,
                    'ten_nhan_vien' => $ten_nhan_vien,
                    'chuc_vu' => $chuc_vu,
                    'dia_chi' => $dia_chi,
                    'so_dien_thoai' => $so_dien_thoai,
                    'id_tosanxuat' => $loai,
                    'ghi_chu' => $ghi_chu,
                   // 'thoi_gian' => $thoi_gian,
               ))
      ->condition('ma_nhan_vien', $ma_nhan_vien)
      ->execute();
  }

  function update_trang_thai_nhan_vien($trang_thai, $ma_nhan_vien, $type) {
    $thoi_gian = time();
    if ($type == 'tiep_tuc') {
      return db_update('dt_nhan_vien')
        ->fields(array(
                      'trang_thai' => $trang_thai,
                      //'thoi_gian' => $thoi_gian
                 ))
        ->condition('ma_nhan_vien', $ma_nhan_vien)
        ->execute();
    }
    else {
      return db_update('dt_nhan_vien')
        ->fields(array(
                      'trang_thai' => $trang_thai,
                      'thoi_gian_ngung' => date('m/Y', $thoi_gian),
                 ))
        ->condition('ma_nhan_vien', $ma_nhan_vien)
        ->execute();
    }
  }

  function validate_ma_nv_tien_do($ma_nv_arr, $type) {
    $data['validate'] = true;
    if (!empty($ma_nv_arr)) {
      $query = db_select('dt_nhan_vien', 'nv');
      $query->fields('nv', array('ma_nhan_vien'));
      $query->condition('nv.ma_nhan_vien', $ma_nv_arr, 'in');
      $result = $query->execute();
      $validate_arr = array();
      while ($rs = $result->fetchObject()) {
        $validate_arr[] = $rs->ma_nhan_vien;
      }
      foreach ($ma_nv_arr as $key => $val) {
        if (!in_array($val, $validate_arr)) {
          $data['validate'] = false;
          $data['key'] = $key;
          if ($type == 'nghi') {
            $data['nghi_nv'] = true;
          }
          if ($type == 'phat') {
            $data['phat_nv'] = true;
          }
          break;
        }
      }
    }
    return $data;
  }

  function insert_nghi_nv($ma_nghi_nv_arr, $ngay_thang, $thoi_luong_nghi_arr, $ly_do_nghi_arr) {
    global $user;
    $query = db_insert('dt_nghi_lam_nhan_vien');
    $i = 0;
    $thoi_gian = time();
    foreach ($ma_nghi_nv_arr as $key => $ma_nv) {
      $arr = array(
        'uid' => $user->uid,
        'ma_nhan_vien' => $ma_nv,
        'ngay_thang' => $ngay_thang,
        'buoi_nghi' => $thoi_luong_nghi_arr[$key],
        'ghi_chu' => $ly_do_nghi_arr[$key],
        'thoi_gian' => $thoi_gian
      );
      if (!$i) {
        $query->fields(array_keys($arr));
      }
      $query->values($arr);
      $i++;
    }
    $query->execute();
  }

  function insert_thuong_phat_nv($type, $ma_phat_nv_arr, $ngay_thang, $tien_phat_nv_arr, $ly_do_phat_nv_arr) {
    global $user;
    $query = db_insert('dt_thuong_phat_nhan_vien');
    $i = 0;
    $thoi_gian = time();
    foreach ($ma_phat_nv_arr as $key => $ma_nv) {
      $arr = array(
        'uid' => $user->uid,
        'type' => $type,
        'ma_nhan_vien' => $ma_nv,
        'ngay_thang' => $ngay_thang,
        'so_tien' => str_replace(',', '', $tien_phat_nv_arr[$key]),
        'ly_do' => $ly_do_phat_nv_arr[$key],
        'ma_bang_luong'=>0,
        'thoi_gian' => $thoi_gian
      );
      if (!$i) {
        $query->fields(array_keys($arr));
      }
      $query->values($arr);
      $i++;
    }
    $query->execute();
  }

  function get_id_nghi_nv_by_date($ngay_thang) {
    $query = db_select('dt_nghi_lam_nhan_vien', 'nl');
    $query->fields('nl', array('id'));
    $query->condition('nl.ngay_thang', $ngay_thang);
    $result = $query->execute();
    $str_id = '';
    while ($rs = $result->fetchObject()) {
      if ($str_id) {
        $str_id .= ',';
      }
      $str_id .= $rs->id;
    }
    return $str_id;
  }

  function get_id_phat_nv_by_date($ngay_thang) {
    $query = db_select('dt_thuong_phat_nhan_vien', 'tp');
    $query->fields('tp', array('id'));
    $query->condition('tp.ngay_thang', $ngay_thang);
    $result = $query->execute();
    $str_id = '';
    while ($rs = $result->fetchObject()) {
      if ($str_id) {
        $str_id .= ',';
      }
      $str_id .= $rs->id;
    }
    return $str_id;
  }